﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Soft000.Model;
using System.Data.Common;
using Soft000.Data;
using System.Reflection;
using Soft000.Common;

namespace Soft000.AccessProvider
{
    /// <summary>
    /// 添加，修改，GetById几个方法中使用了反射，考虑到效率问题，可对其进行重写
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class Base<T> where T : class, new()
    {
        protected string TableName = "";
        protected string KeyField = "id";

        #region 分页查询
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="strFileds">返回的字段，合部用"*"</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="strOrders">排序字段</param>
        /// <param name="nNowPage">当前页</param>
        /// <param name="nPageSize">页的大小</param>
        /// <param name="ht">调用此存储过程最终所执行的SQL语句,输出参数，方便调试</param>
        /// <returns>DataSet数据集</returns>
        public DataSet Paging(string strFileds, string strWhere, string strOrders, int nNowPage, int nPageSize, ref string strSql)
        {
            return Paging(TableName, strFileds, strWhere, strOrders, KeyField, 1, nNowPage, nPageSize, ref strSql);
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="strTableName">数据表名</param>
        /// <param name="strFileds">返回的字段，合部用"*"</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="strKeyFields">关键字，分页字段，此处为自动增长Id</param>
        /// <param name="strOrders">排序字段</param>
        /// <param name="KeyOrder">0按关键字升序分页 1按关键字降序分页</param>
        /// <param name="nNowPage">当前页</param>
        /// <param name="nPageSize">页的大小</param>
        /// <param name="ht">调用此存储过程最终所执行的SQL语句,输出参数，方便调试</param>
        /// <returns>DataSet数据集</returns>
        public DataSet Paging(string strTableName, string strFileds, string strWhere, string strOrders, string strKeyFields, int KeyOrder, int nNowPage, int nPageSize, ref string strSql)
        {
            if (nNowPage < 1)
                nNowPage = 1;
            if (nPageSize < 1)
                nPageSize = 1;
            string _strSql = "select top "+nPageSize.ToString()+" * from "+TableName;
            if (nNowPage > 1)
            {
                _strSql += " "+strWhere+" and "+KeyField+" not in (select top "+KeyField+" from "+TableName+" "+strWhere+" "+strOrders+") "+strOrders;
            }
            else
            {
                _strSql += " "+strWhere+" "+strOrders;
            }
            strSql = _strSql;
            DataSet ds = DbHelper.ExecuteDataSet(CommandType.Text,_strSql);
            
            return ds;
        }
        #endregion

        #region 返回记录数
        /// <summary>
        /// 返回记录数
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <param name="strSql">存储过程最终执行的SQL语句，输出，方便出错时的调试，不需要可传null</param>
        /// <returns>返回记录数</returns>
        public long GetRecordCount(string strWhere, ref string strSql)
        {
            strSql = "select count(1) from ["+TableName+"] "+strWhere;
            object ret=DbHelper.ExecuteScalar(CommandType.Text, strSql);
            return Convert.ToInt64(ret);
        }
        #endregion

        #region 通用数据删除方法
        /// <summary>
        /// 数据删除
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <returns>返回所影响的行数</returns>
        public virtual int Delete(long id)
        {
            string strSql = "delete from " + TableName + " where [" + KeyField + "]=" + id.ToString();
            return DbHelper.ExecuteNonQuery(CommandType.Text, strSql);
        }
        #endregion

        #region 通用的添加修改方法(以下方法使用了反射)

        #region 根据Id返回数据对像
        /// <summary>
        /// 根据Id返回数据对像
        /// </summary>
        /// <param name="id">数据库主键ID</param>
        /// <returns>成功返回数据模型，失败返回NULL</returns>
        public virtual T GetById(long id)
        {
            string strSql = "Select * from [" + TableName + "] where [" + KeyField + "]=" + id.ToString();
            return GetInfoBySql(strSql);
        }
        public T GetInfoBySql(string strSql)
        {
            T info = (T)null;
            using (OleDbDataReader r = (OleDbDataReader)DbHelper.ExecuteReader(CommandType.Text, strSql))
            {
                info = GetInfoByReader(r);
                r.Close();
                r.Dispose();
            }
            return info;
        }
        public T GetInfoByReader(OleDbDataReader reader)
        {
            T info = (T)null;
            if (reader.Read())
            {
                info = new T();
                Type tp = info.GetType();
                foreach (PropertyInfo o in tp.GetProperties())
                {
                    o.SetValue(info, reader[o.Name], null);
                }
            }
            return info;
        }
        #endregion

        #region 添加
        /// <summary>
        /// 数据添加
        /// </summary>
        /// <param name="info">数据模型</param>
        /// <returns>成功返回true,失败返false</returns>
        public virtual bool Add(T info)
        {
            List<System.Data.OleDb.OleDbParameter> parm = new List<System.Data.OleDb.OleDbParameter>();
            string strFileds = "";
            string strValues = "";
            Type tp = info.GetType();
            foreach (PropertyInfo o in tp.GetProperties())
            {
                object oValue = o.GetValue(info, null);
                if (o.Name.ToLower().Trim() == KeyField.ToLower().Trim() && (oValue.GetType() == typeof(int) || oValue.GetType() == typeof(long)) && Convert.ToInt32(oValue) == 0)
                {
                    continue;
                }
                if (strFileds == "")
                {
                    strFileds = "[" + o.Name + "]";
                    strValues = "@" + o.Name;
                }
                else
                {
                    strFileds += ",[" + o.Name + "]";
                    strValues += ",@" + o.Name;
                }
                DbParameter p;
                if (o.PropertyType == typeof(DateTime))
                {
                    p = DbHelper.MakeInParam("@" + o.Name, DbType.Date, 0, oValue);
                    p.DbType = DbType.Date;
                }
                else
                {
                    p = DbHelper.MakeInParam("@" + o.Name, DbType.Date, 0, oValue);
                }
                parm.Add(p);
            }
            OleDbParameter []_parm=new OleDbParameter[parm.Count];
            for(int i=0;i<parm.Count;i++)
            {
                _parm[i]=parm[i];
            }
            return DbHelper.ExecuteNonQuery(CommandType.Text, "insert into " + TableName + " (" + strFileds + ") values(" + strValues + ")",_parm) > 0;
        }
        #endregion

        #region 修改
        /// <summary>
        /// 数据添加
        /// </summary>
        /// <param name="info">数据模型</param>
        /// <returns>成功返回true,失败返false</returns>
        public virtual bool Update(T info)
        {
            List<System.Data.OleDb.OleDbParameter> parm = new List<System.Data.OleDb.OleDbParameter>();
            string strFileds = "";
            object oKeyValue = "0";
            Type tp = info.GetType();
            foreach (PropertyInfo o in tp.GetProperties())
            {
                string temp = o.Name.ToLower();
                object oValue = o.GetValue(info, null);
                if (temp.Trim().ToLower() == KeyField.Trim().ToLower())
                {
                    oKeyValue = o.GetValue(info, null);
                    continue;
                }
                if (strFileds == "")
                {
                    strFileds = "[" + o.Name + "]=@" + o.Name;
                }
                else
                {
                    strFileds += ",[" + o.Name + "]=@" + o.Name;
                }
                DbParameter p;
                if (o.PropertyType == typeof(DateTime))
                {
                    p = DbHelper.MakeInParam("@" + o.Name, DbType.Date, 0, oValue);
                    p.DbType = DbType.Date;
                }
                else
                {
                    p = DbHelper.MakeInParam("@" + o.Name, DbType.Date, 0, oValue);
                }
                parm.Add(p);
            }
            OleDbParameter[] _parm = new OleDbParameter[parm.Count];
            for (int i = 0; i < parm.Count; i++)
            {
                _parm[i] = parm[i];
            }
            return DbHelper.ExecuteNonQuery(CommandType.Text, "Update " + TableName + " set " + strFileds + " where " + KeyField + "=" + oKeyValue.ToString(), _parm) > 0;
        }
        #endregion
        #endregion
    }
}
